package com.zc.controller;


import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zc.common.Result;
import com.zc.dto.ExamScoreTrendDTO;
import com.zc.dto.ScoreAddDTO;
import com.zc.dto.ScoreStudentDTO;
import com.zc.exception.CustomerException;
import com.zc.pojo.Exams;
import com.zc.pojo.Scores;
import com.zc.pojo.Students;
import com.zc.service.ExamsService;
import com.zc.service.ScoresService;
import com.zc.service.StudentsService;
import jakarta.annotation.Resource;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/scores")
public class ScoresController {

    @Resource
    private ScoresService scoresService;
    @Resource
    private ExamsService examsService;
    @Resource
    private StudentsService studentsService;

    /**
     * 获取学生所有考试的成绩趋势数据
     *
     * @param studentId 学生ID
     * @return 成绩趋势数据列表
     */
    @GetMapping("/trend/{studentId}")
    public Result getScoreTrend(@PathVariable Integer studentId) {
        List<ExamScoreTrendDTO> trendData = scoresService.getStudentScoreTrend(studentId);
        return Result.success(trendData);
    }

    @GetMapping("/page")
    public Result selectAll(@RequestParam Integer pageNum,
                            @RequestParam Integer pageSize,
                            @RequestParam(required = false) String name,
                            @RequestParam(required = false) Integer selectedExam,
                            @RequestParam(required = false) String selectedClass) {
        // 创建分页对象（注意泛型是DTO）
        Page<ScoreStudentDTO> page = new Page<>(pageNum, pageSize);

        // 调用关联查询方法
        IPage<ScoreStudentDTO> resultPage = scoresService.selectScoreWithStudent(page, name, selectedExam, selectedClass);

        return Result.success(resultPage);
    }

    @Transactional
    @DeleteMapping("/clearExamData")
    public Result clearExamData(@RequestParam Integer selectedExam) {
        if (selectedExam == null) {
            return Result.error("考试ID不能为空"); // 增加参数校验
        }
        LambdaQueryWrapper<Scores> lmq = new LambdaQueryWrapper<>();
        lmq.eq(Scores::getExamId, selectedExam);
        boolean isRemoved = scoresService.remove(lmq);
        if (isRemoved) {
            return Result.success("清除成功");
        } else {
            return Result.error("清除失败（可能无对应数据）");
        }
    }

    @GetMapping("/{id}")
    public Result selectById(@PathVariable Integer id) {
        return Result.success(scoresService.getById(id));
    }


    @PostMapping("/add")
    public Result addScore(@RequestBody ScoreAddDTO dto) {
        // 1. 通过学生姓名查询student_id
        Students student = studentsService.getByName(dto.getStudentName());
        if (student == null) {
            return Result.error("学生不存在，请检查姓名");
        }
        Integer studentId = student.getStudentId();

        // 2. 通过考试名称查询exam_id
        Exams exam = examsService.getByName(dto.getExamName());
        if (exam == null) {
            return Result.error("考试不存在，请检查考试名称");
        }
        Integer examId = exam.getExamId();

        // 3. 检查是否已存在该学生的该考试成绩（避免违反UNIQUE约束）
        boolean exists = scoresService.exists(studentId, examId);
        if (exists) {
            return Result.error("该学生已存在该考试成绩，请勿重复添加");
        }

        // 4. 构建Scores对象并插入数据库
        Scores scores = new Scores();
        scores.setStudentId(studentId);
        scores.setExamId(examId);
        scores.setScore(dto.getScore());

        boolean save = scoresService.save(scores);
        return save ? Result.success("添加成功") : Result.error("添加失败");
    }


    @PutMapping("/update")
    public Result updateById(@RequestBody Scores scores) {
        return Result.success(scoresService.updateById(scores));
    }


    @DeleteMapping("/delete/{id}")
    public Result delete(@PathVariable Integer id) {
        if (id == null) {
            return Result.error("删除失败：ID不能为空");
        }

        // 使用 Service 层的方法
        Scores exists = scoresService.getById(id);
        if (exists == null) {
            return Result.error("删除失败：该成绩记录不存在");
        }

        boolean isRemoved = scoresService.removeById(id);
        return isRemoved ? Result.success("删除成功") : Result.error("删除失败");
    }

    @PostMapping("/deleteBatch")
    public Result deleteBatch(@RequestBody List<Integer> ids) {
        // 增加参数校验
        if (ids == null || ids.isEmpty()) {
            return Result.error("请选择要删除的数据");
        }
        boolean success = scoresService.removeBatchByIds(ids);
        return success ? Result.success("删除成功") : Result.error("删除失败");
    }

    //数据导出
    @GetMapping("/export")
    public void exportData(@RequestParam(required = false) String name,
                           @RequestParam(required = false) Integer examId,
                           @RequestParam(required = false) String selectedClass,
                           @RequestParam(required = false) String ids,
                           HttpServletResponse response) throws Exception {
        if (examId == null) {
            throw new CustomerException("考试ID不能为空");
        }
        // 1. 根据是否有ids，查询对应数据
        List<ScoreStudentDTO> exportData;
        if (ids != null && !ids.isEmpty()) {
            // 场景1：有选中数据，优先导出选中的studentId对应的数据
            List<Integer> studentIds = Arrays.stream(ids.split(","))
                    .map(Integer::parseInt)
                    .collect(Collectors.toList());
            exportData = scoresService.exportSelectedStudents(studentIds);
        } else {
            // 场景2：无选中数据，按筛选条件导出
            exportData = scoresService.exportScoreWithStudent(name, examId, selectedClass);
        }
        String excelName = "成绩数据导出";  // 默认文件名

        Exams one = examsService.getById(examId);
        if (one != null && one.getExamName() != null) {
            excelName = one.getExamName();  // 替换为实际考试名称
        }
        // 2. Hutool 生成Excel并响应
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String fileName = URLEncoder.encode(excelName, StandardCharsets.UTF_8);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        // 3. 使用Hutool的ExcelWriter写入数据
        try (OutputStream out = response.getOutputStream();

             ExcelWriter writer = ExcelUtil.getWriter(true)) {
            // 自定义表头别名（字段名 -> 中文表头）
            //writer.addHeaderAlias("scoreId", "成绩ID");
            //writer.addHeaderAlias("studentId", "学生ID");
            //writer.addHeaderAlias("examId", "考试ID");
            writer.addHeaderAlias("className", "班级");
            writer.addHeaderAlias("name", "学生姓名");
            writer.addHeaderAlias("studentNumber", "学号");
            writer.addHeaderAlias("seatNumber", "座号");
            writer.addHeaderAlias("score", "成绩");
            writer.addHeaderAlias("classRank", "班级排名");
            writer.addHeaderAlias("gradeRank", "年级排名");

            // 写入数据（第二个参数true表示使用别名作为表头）
            writer.setOnlyAlias(true);//排除字段操作
            writer.write(exportData, true);
            writer.autoSizeColumnAll(); // 自动调整列宽
            writer.flush(out, true);
        }
    }


    @PostMapping("/import")
    @Transactional(rollbackFor = Exception.class)
    public Result importData(
            MultipartFile file,
            @RequestParam Integer examId
    ) throws Exception {
        // 存储所有校验通过的数据（最终一次性保存）
        List<Scores> allValidScores = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream();
             ExcelReader reader = ExcelUtil.getReader(inputStream)) {
            // 读取所有行数据（Map的key为表头名称，value为单元格值）
            List<Map<String, Object>> rows = reader.readAll();

            // 遍历每一行数据（从0开始，行号+2对应Excel实际行号）
            for (int i = 0; i < rows.size(); i++) {
                int excelRowNum = i + 2; // Excel实际行号（表头占1行）
                Map<String, Object> rowData = rows.get(i);

                // 1. 解析Excel单元格数据（只处理需要的字段）
                //班级用于匹配有学生换班了
                String className = rowData.get("班级") != null ?
                        String.valueOf(rowData.get("班级")).trim() : "";
                // 姓名：必填（用于匹配学生）
                String studentName = rowData.get("姓名") != null ?
                        String.valueOf(rowData.get("姓名")).trim() : "";
                // 成绩：可为空或非数字（处理为null表示缺考）
                // 先检查"成绩"列是否有值，有则使用；否则检查"数学"列
                String scoreStr = "";
                if (rowData.get("成绩") != null) {
                    scoreStr = String.valueOf(rowData.get("成绩")).trim();
                } else if (rowData.get("数学") != null) {
                    scoreStr = String.valueOf(rowData.get("数学")).trim();
                }
                // 2. 校验关键数据
                if (className.isEmpty() || "null".equals(className)) {
                    throw new CustomerException("第" + excelRowNum + "行：班级不能为空");
                }
                if (studentName.isEmpty() || "null".equals(studentName)) {
                    throw new CustomerException("第" + excelRowNum + "行：姓名不能为空");
                }
                // 3. 处理成绩（空或非数字时设为null表示缺考）
                BigDecimal score = null;
                if (!scoreStr.isEmpty()) {
                    try {
                        score = new BigDecimal(scoreStr);
                    } catch (NumberFormatException e) {
                        // 非数字格式视为缺考，保持score为null
                    }
                }

                // 4. 根据姓名查询学生（不校验班级，直接通过姓名匹配）
                Students student = studentsService.getOne(new QueryWrapper<Students>()
                        .eq("name", studentName)
                        .eq("class_name", className));
                if (student == null) {
                    throw new CustomerException("第" + excelRowNum + "行：未找到班级为【" + className + "】" + "姓名为【" + studentName + "】的学生");
                }

                // 5. 检查是否已存在相同记录（student_id + exam_id唯一约束）
                boolean isDuplicate = scoresService.exists(new QueryWrapper<Scores>()
                        .eq("student_id", student.getStudentId())
                        .eq("exam_id", examId));
                if (isDuplicate) {
                    throw new CustomerException("第" + excelRowNum + "行：学生【" + studentName + "】的该考试成绩已存在");
                }

                // 6. 构建成绩对象（不包含班级相关字段）
                Scores scoreRecord = new Scores();
                scoreRecord.setStudentId(student.getStudentId()); // 关联学生ID
                scoreRecord.setExamId(examId); // 关联考试ID（前端传入）
                scoreRecord.setScore(score); // 成绩（可能为null表示缺考）

                // 7. 加入校验通过列表
                allValidScores.add(scoreRecord);
            }

            // 8. 所有数据校验通过后，批量保存（事务保证要么全成功，要么全失败）
            if (!allValidScores.isEmpty()) {
                scoresService.saveBatch(allValidScores);
            }
        }
        // 9. 返回成功结果
        return Result.success("导入成功，共" + allValidScores.size() + "条数据");
    }

}

